set doc off
set verify off
--
--    Program: CAPGLEXT_RATE_PERIOD.sql
--    Description:
--       Extract period rate associated with the SOB that the user specified
--
--    Revision:
--       1st     
--    Parameter: 1. set of books short name e.g. SOB_C999

--whenever sqlerror exit failure rollback
set serveroutput on size 1000000;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
                    		
   l_utl_file       		utl_file.file_type;
   l_filename       		varchar2(255);
   l_pathname       		varchar2(100);
                    		
   l_ngo_sob				varchar2(20);
   
   cursor cur_s (p_ngo_sob varchar2) is
		select * 
		from gl_translation_rates 
		where set_of_books_id = (select set_of_books_id 
		                         from gl_sets_of_books
		                         where upper(short_name) = upper(p_ngo_sob));
		

begin
   l_pathname := '/tmp';
   l_filename := 'CAPGL_RATE_PERIOD.dat';
   l_utl_file := utl_file.fopen(l_pathname, l_filename, 'w');
   
   
   l_processed_count := 0;
   l_rejected_count  := 0;
   
   l_ngo_sob := '&1';
       
   for s in cur_s(l_ngo_sob) loop
     
     utl_file.put_line( l_utl_file, 
       s.SET_OF_BOOKS_ID       ||chr(9)||
       s.PERIOD_NAME           ||chr(9)||
       s.TO_CURRENCY_CODE      ||chr(9)||
       s.ACTUAL_FLAG           ||chr(9)||
       s.AVG_RATE              ||chr(9)||
       s.EOP_RATE_NUMERATOR    ||chr(9)||
       s.EOP_RATE_DENOMINATOR  ||chr(9)||
       s.AVG_RATE_NUMERATOR    ||chr(9)||
       s.AVG_RATE_DENOMINATOR  ||chr(9)||
       s.EOP_RATE              ||chr(9)||
       s.UPDATE_FLAG           ||chr(9)||
       to_char(s.LAST_UPDATE_DATE, 'DD-MON-YYYY HH24:MI:SS') ||chr(9)||
       s.LAST_UPDATED_BY       ||chr(9)||
       to_char(s.CREATION_DATE, 'DD-MON-YYYY HH24:MI:SS') ||chr(9)||
       s.CREATED_BY            ||chr(9)||
       s.LAST_UPDATE_LOGIN     ||chr(9)||
       s.ATTRIBUTE1            ||chr(9)||
       s.ATTRIBUTE2            ||chr(9)||
       s.ATTRIBUTE3            ||chr(9)||
       s.ATTRIBUTE4            ||chr(9)||
       s.ATTRIBUTE5            ||chr(9)||
       s.CONTEXT               ||chr(9)||
       CAPBREXT_COMMON.GetUser(s.LAST_UPDATED_BY)|| chr(9) ||
       CAPBREXT_COMMON.GetUser(s.CREATED_BY)|| chr(9) ||
       CAPBREXT_COMMON.GetSOBShortName(s.SET_OF_BOOKS_ID)
     );
     
     l_processed_count := l_processed_count + 1;

   end loop;       
   
   if l_processed_count = 0 then
     l_rejected_count := l_rejected_count + 1;
     l_err_msg := 'No Period Rate defined for this NGO '||l_ngo_sob;
   end if;
      
   -- insert into log table
   insert into capcnv_log ( prog_id, process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPGLEXT_RATE_PERIOD', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;

   utl_file.fclose(l_utl_file);  
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;
end;
/

--exit
